Imports System.Windows.Forms

Public Class frmCalcLoad

    Private Enum enumAggregate
        Hour
        Day
        Week
        Month
        CalendarYear
        WaterYear
        ClimaticYear
        Growing
        Season
    End Enum

    Private CalcCrossTab, CalcAggregate As Boolean 'flags set to true initially and when options changed

    Private Sub frmCalcLoad_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        SaveControlValues(REGAPP, Me)
        If cboAggregation.Text.StartsWith("Four") Then
            SaveSetting(REGAPP, Name, "WinterMonth", cboMonth1.Text)
            SaveSetting(REGAPP, Name, "WinterDay", cboDay1.Text)
        ElseIf cboAggregation.Text.StartsWith("Growing") Then
            SaveSetting(REGAPP, Name, "GrowingMonth1", cboMonth1.Text)
            SaveSetting(REGAPP, Name, "GrowingDay1", cboDay1.Text)
            SaveSetting(REGAPP, Name, "GrowingMonth2", cboMonth2.Text)
            SaveSetting(REGAPP, Name, "GrowingDay2", cboDay2.Text)
        End If
    End Sub

    Private Sub frmCalcLoad_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Project.DB.FillPickList(cboConc, clsProject.TempData, "PCodes", "PCode", "Anal_Name")
        Project.DB.FillPickList(cboFlow, clsProject.TempData, "PCodes", "PCode", "Anal_Name")
        Project.DB.FillPickList(cboLoad, "", "PCodes", "PCode", "Anal_Name", "(Create New)")
        Project.DB.FillPickList(cboCCode, "", "CCodes", "CCode", "CCode_Name", "(Create New)")
        txtMult.Text = GetSetting(REGAPP, Name, "txtMult", "3.7854")
        cboAggregation.Text = GetSetting(REGAPP, Name, "cboAggregation", "None")
        CalcCrossTab = True
        CalcAggregate = True
        frmCalc.RefreshTableList(cboTableName)
        GetControlValues(REGAPP, Me)
    End Sub

    Private Sub cboTableName_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboTableName.SelectionChangeCommitted
        frmCalc.TableSelectionCommitted(cboTableName)
    End Sub

    Private Sub btnNext_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnNext.Click
        Try
            With TabControl1
                If .SelectedIndex = 2 Then
                    'save computed values to table
                    Dim TableName As String = cboTableName.Text
                    If cboTableName.SelectedIndex = 0 Then TableName = ActiveBrowser.FullTableName
                    Dim DataTable As New clsDataTable(Project, TableName)
                    DataTable.BeginTransaction()
                    Try
                        With dgAggregated
                            Dim NumCols As Integer = 1
                            Dim NumComputed As Integer = NumCols * .Rows.Count
                            If MessageBox.Show(StringFormat("You are about to append {0} computed values to the selected Working table ({1}). These were calculated based upon the input PCodes you selected for concentration ({2}) and flow ({3}). The calculated values will use the PCode {4} and CCode {5} and apply the multiplier you specified ({6}).\n\nBecause the table will be permanently modified, it may be difficult to undo this operation. Are you sure you want to continue?", NumComputed, TableName, cboConc.Text, cboFlow.Text, cboLoad.Text, cboCCode.Text, txtMult.Text), "Calculate Loads", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) = Windows.Forms.DialogResult.OK Then
                                Dim PCode As String = Project.DB.GetPickList(cboLoad)
                                Dim CCode As String = Project.DB.GetPickList(cboCCode)
                                If PCode.StartsWith("(Create") Or CCode.StartsWith("(Create") Then
                                    WarningMsg("You must select a valid PCode and CCode before continuing.")
                                    Exit Sub
                                End If

                                MainForm.WriteProgress("Deleting old records...")
                                If Not PCode.StartsWith("(") Then
                                    DataTable.DeleteRecords("PCode='{0}'", PCode)
                                End If
                                Try
                                    For i As Integer = 0 To .Rows.Count - 1
                                        If Not PCode.StartsWith("(") Then
                                            With .Rows(i)
                                                Dim DataRecord As New clsDataRecord(.Cells(0).Value, .Cells(1).Value, PCode, .Cells(2).Value, Project.DB.GetPickList(cboCCode))
                                                If Not DataTable.InsertRecord(DataRecord) Then WarningMsg("Unable to update table.") : Exit Sub
                                            End With
                                        End If
                                        MainForm.WriteProgress(i, .Rows.Count - 1, "Saving computed values...", ProgressBarStyle.Continuous)
                                    Next
                                    DataTable.Close(clsDataTable.enumEndTransaction.Commit)
                                    IniFile.SetKey(clsWRDBIniFile.enumSection.Application, clsWRDBIniFile.enumKey.AppLastEditTime, Now.ToString(System.Globalization.CultureInfo.InvariantCulture.DateTimeFormat))
                                Catch ex As Exception
                                    DataTable.Close(clsDataTable.enumEndTransaction.Rollback)
                                End Try
                                MainForm.ProgressComplete()
                                Project.UpdateTrackingRecord(TableName, , True)
                                MainForm.OpenDataTable(TableName)
                                Project.DataSet.Tables.Remove("CrossTab")
                                Close()
                            End If
                        End With
                    Catch ex As Exception
                        Throw
                    Finally
                        DataTable.Close()
                    End Try
                Else
                    .SelectedIndex += 1
                End If
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        Finally
            MainForm.ProgressComplete()
            IniFile.Save()
        End Try
    End Sub

    Private Sub TabControl1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TabControl1.SelectedIndexChanged
        Try
            Select Case TabControl1.SelectedIndex
                Case 1
                    Dim NotSelected As Boolean = False
                    For i As Integer = 1 To 4
                        With CType(Choose(i, cboConc, cboFlow, cboLoad, cboCCode), ComboBox)
                            If .SelectedIndex = -1 Then NotSelected = True : Exit For
                        End With
                    Next
                    If NotSelected Then
                        WarningMsg("You must specify all PCodes and the CCode before you can continue.")
                        TabControl1.SelectedIndex = 0
                        Exit Sub
                    End If
            End Select
            If TabControl1.SelectedIndex = 2 Then
                btnNext.Text = "&Finish"
            Else
                btnNext.Text = "&Next"
            End If
            If TabControl1.SelectedIndex >= 1 And CalcCrossTab Then 'create raw data crosstab
                dgRecords.DataSource = Nothing
                MainForm.WriteProgress("Creating crosstab...", ProgressBarStyle.Marquee, False)
                Project.DB.AddTable(Project.DataSet, "CrossTab", String.Format("SELECT D1.Station_ID AS Station_ID,D1.Date_Time AS Date_Time,D1.Result as CONC,D2.Result as FLOW,0.0 as CALCLOAD FROM {0} D1, {0} D2 WHERE D1.Station_ID=D2.Station_ID AND D1.Date_Time=D2.Date_Time and D1.PCode='{1}' and D2.PCode='{2}' ORDER BY Station_ID,Date_Time", clsProject.TempData, Project.DB.GetPickList(cboConc), Project.DB.GetPickList(cboFlow)))
                For Each r As DataRow In Project.DataSet.Tables("CrossTab").Rows
                    r.Item(4) = r.Item(2) * r.Item(3) * Val(txtMult.Text)
                Next
                With dgRecords
                    .DataSource = Project.DataSet.Tables("CrossTab")
                    .ReadOnly = True
                    .RowHeadersVisible = False
                    .AllowUserToAddRows = False
                    .AllowUserToDeleteRows = False
                    .AllowUserToOrderColumns = False
                    .AllowUserToResizeRows = False
                    .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                    For c As Integer = 0 To 4
                        With .Columns(c)
                            .SortMode = DataGridViewColumnSortMode.NotSortable
                            .HeaderText = Choose(c + 1, "Station ID", "Date-Time", "Conc", "Flow", "Load")
                            .DefaultCellStyle.Format = Choose(c + 1, "", MyDateTimeFormat, "N3", "N3", "N3")
                            .DefaultCellStyle.Alignment = Choose(c + 1, DataGridViewContentAlignment.MiddleLeft, DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleRight, DataGridViewContentAlignment.MiddleRight, DataGridViewContentAlignment.MiddleRight)
                            .AutoSizeMode = Choose(c + 1, DataGridViewAutoSizeColumnMode.AllCells, DataGridViewAutoSizeColumnMode.AllCells, DataGridViewAutoSizeColumnMode.Fill, DataGridViewAutoSizeColumnMode.Fill, DataGridViewAutoSizeColumnMode.Fill)
                        End With
                    Next
                End With
                CalcCrossTab = False
                CalcAggregate = True
            End If

            If TabControl1.SelectedIndex = 2 And CalcAggregate Then 'aggregate the data
                'create an array of hourly loads from the first date to the last date
                With dgAggregated
                    .Columns.Clear()
                    .ReadOnly = True
                    .RowHeadersVisible = False
                    .AllowUserToAddRows = False
                    .AllowUserToDeleteRows = False
                    .AllowUserToOrderColumns = False
                    .AllowUserToResizeRows = False
                    .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                    For c As Integer = 0 To 2
                        .Columns.Add(Choose(c + 1, "Station_ID", "Date_Time", "Load"), Choose(c + 1, "Station ID", "Date-Time", "Load"))
                        With .Columns(c)
                            .SortMode = DataGridViewColumnSortMode.NotSortable
                            .DefaultCellStyle.Format = Choose(c + 1, "", MyDateTimeFormat, "N3")
                            .DefaultCellStyle.Alignment = Choose(c + 1, DataGridViewContentAlignment.MiddleLeft, DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleRight)
                            .AutoSizeMode = Choose(c + 1, DataGridViewAutoSizeColumnMode.AllCells, DataGridViewAutoSizeColumnMode.AllCells, DataGridViewAutoSizeColumnMode.Fill)
                        End With
                    Next
                End With

                Dim dt As DataTable = Project.DataSet.Tables("CrossTab")
                Dim al As New ArrayList
                Dim MinDate As Date = dt.Rows(0).Item(1)
                Dim MaxDate As Date = dt.Rows(dt.Rows.Count - 1).Item(1)
                Dim AggStep As enumAggregate = cboAggregation.SelectedIndex
                Select Case AggStep
                    Case enumAggregate.Hour
                        With MinDate : MinDate = New Date(.Year, .Month, .Day, .Hour, 0, 0) : End With
                        With MaxDate : MaxDate = New Date(.Year, .Month, .Day, .Hour, 0, 0) : End With
                        MaxDate = MaxDate.AddHours(1)
                    Case enumAggregate.Day
                        With MinDate : MinDate = New Date(.Year, .Month, .Day) : End With
                        With MaxDate : MaxDate = New Date(.Year, .Month, .Day) : End With
                        MaxDate = MaxDate.AddDays(1)
                    Case enumAggregate.Week
                        With MinDate : MinDate = New Date(.Year, .Month, .Day) : End With
                        MinDate = MinDate.AddDays(-MinDate.DayOfWeek) 'change to first day of week (sunday)
                        With MaxDate : MaxDate = New Date(.Year, .Month, .Day) : End With
                        MaxDate = MaxDate.AddDays(7 - MaxDate.DayOfWeek + 1)
                    Case enumAggregate.Month
                        With MinDate : MinDate = New Date(.Year, .Month, 1) : End With
                        With MaxDate : MaxDate = New Date(.Year, .Month, 1) : End With
                        MaxDate = MaxDate.AddMonths(1)
                    Case enumAggregate.CalendarYear
                        With MinDate : MinDate = New Date(.Year, 1, 1) : End With
                        With MaxDate : MaxDate = New Date(.Year, 1, 1) : End With
                        MaxDate = MaxDate.AddYears(1)
                    Case enumAggregate.ClimaticYear
                        With MinDate : MinDate = New Date(IIf(.Month < 4, .Year - 1, .Year), 4, 1) : End With
                        MaxDate = MinDate.AddYears(1)
                    Case enumAggregate.Growing
                        With MinDate : MinDate = New Date(IIf(.DayOfYear < New Date(.Year, cboMonth1.SelectedIndex + 1, cboDay1.SelectedIndex + 1).DayOfYear, .Year, .Year + 1), cboMonth1.SelectedIndex + 1, cboDay1.SelectedIndex + 1) : End With
                        With MaxDate : MaxDate = New Date(.Year, cboMonth2.SelectedIndex + 1, cboDay2.SelectedIndex + 1) : End With
                    Case enumAggregate.Season
                        With MinDate
                            Dim MinDateWinter As Date = New Date(.Year, cboMonth1.SelectedIndex + 1, cboDay1.SelectedIndex + 1)
                            For s As Integer = 0 To 3
                                If .DayOfYear >= MinDateWinter.AddMonths(s).DayOfYear And .DayOfYear < MinDateWinter.AddMonths(s + 1).DayOfYear Then
                                    MinDate = MinDateWinter.AddMonths(s)
                                End If
                            Next
                        End With
                        With MaxDate
                            Dim MinDateWinter As Date = New Date(.Year, cboMonth1.SelectedIndex + 1, cboDay1.SelectedIndex + 1)
                            For s As Integer = 0 To 3
                                If .DayOfYear >= MinDateWinter.AddMonths(s).DayOfYear And .DayOfYear < MinDateWinter.AddMonths(s + 1).DayOfYear Then
                                    MaxDate = MinDateWinter.AddMonths(s)
                                End If
                            Next
                        End With
                End Select

                'form hourly loads
                Dim ThisDate As Date = MinDate, ThisLoad As Single
                Dim StaID As String = dt.Rows(0).Item(0)

                Dim r As Integer = 1
                Do
                    If dt.Rows.Count <= 2 Then WarningMsg("You must have at least two load values to use this routine.") : Exit Sub
                    If dt.Rows(r).Item(0) <> StaID Then
                        WarningMsg("This tool only works for a single selected Station ID.")
                        Exit Sub
                    End If
                    Dim PrevDate As Date = dt.Rows(r - 1).Item(1)
                    Dim NextDate As Date = dt.Rows(r).Item(1)
                    Dim PrevLoad As Single = dt.Rows(r - 1).Item(4)
                    Dim NextLoad As Single = dt.Rows(r).Item(4)
                    If ThisDate.CompareTo(PrevDate) < 0 Or ThisDate.CompareTo(dt.Rows(dt.Rows.Count - 1).Item(1)) > 0 Then 'is before beginning or after end
                        ThisLoad = Single.MinValue
                    ElseIf ThisDate.CompareTo(NextDate) > 0 Then
                        r += 1
                        Continue Do
                    Else
                        ThisLoad = InterpolateY(ThisDate, PrevDate, PrevLoad, NextDate, NextLoad)
                    End If
                    al.Add(ThisLoad)
                    ThisDate = ThisDate.AddHours(1)
                    If ThisDate.CompareTo(dt.Rows(r).Item(1)) = 1 Then r += 1
                Loop Until ThisDate > MaxDate Or r > dt.Rows.Count - 1

                'average the values within the specified time periods
                ThisDate = MinDate
                Dim i As Integer = 0

                Do
                    Dim PrevDate As Date = ThisDate
                    Dim NextDate As Date
                    Select Case AggStep
                        Case enumAggregate.Hour : NextDate = ThisDate.AddHours(1)
                        Case enumAggregate.Day : NextDate = ThisDate.AddDays(1)
                        Case enumAggregate.Week : NextDate = ThisDate.AddDays(7)
                        Case enumAggregate.Month : NextDate = ThisDate.AddMonths(1)
                        Case enumAggregate.CalendarYear To enumAggregate.ClimaticYear : NextDate = ThisDate.AddYears(1)
                        Case enumAggregate.Growing : NextDate = New Date(ThisDate.Year, cboMonth2.SelectedIndex + 1, cboDay1.SelectedIndex + 1)
                        Case enumAggregate.Season : NextDate = ThisDate.AddMonths(3)
                    End Select
                    Dim Sum As Single = 0
                    Dim Num As Integer = 0
                    Do
                        If al(i) <> Single.MinValue Then
                            Sum += al(i)
                            Num += 1
                            ' Debug.Print(Num & vbTab & al(i))
                        End If
                        i += 1
                        ThisDate = ThisDate.AddHours(1)
                    Loop Until ThisDate >= NextDate Or i > al.Count - 1
                    dgAggregated.Rows.Add(StaID, PrevDate, Sum / Num)

                    If AggStep = enumAggregate.Growing Then
                        Dim NextSpring As Date = New Date(NextDate.Year + 1, cboMonth1.SelectedIndex + 1, cboDay1.SelectedIndex + 1)
                        Do
                            i += 1
                            If i > al.Count - 1 Then
                                Exit Do
                            Else
                                ThisDate = ThisDate.AddHours(1)
                            End If
                        Loop Until ThisDate >= NextSpring
                        NextDate = NextSpring
                    End If
                    If Not MainForm.WriteProgress(i, al.Count - 1, "Aggregating data...", ProgressBarStyle.Marquee, True) Then Exit Do
                Loop Until ThisDate > MaxDate Or i > al.Count - 1
                CalcAggregate = False
            End If
        Catch ex As Exception
            ErrorMsg(, ex)
        Finally
            MainForm.ProgressComplete()
        End Try
    End Sub

    Private Sub btnHelp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHelp.Click
        ShowHelp(Me, "20")
    End Sub

    Private Sub cboAggregation_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboAggregation.SelectedIndexChanged
        If cboAggregation.Text.StartsWith("Four") Then
            lblDate1.Text = "Winter Starting Date:"
            cboMonth1.Text = GetSetting(REGAPP, Name, "WinterMonth", "January")
            cboDay1.Text = GetSetting(REGAPP, Name, "WinterDay", 1)
            tblDate1.Visible = True
            tblDate2.Visible = False
        ElseIf cboAggregation.Text.StartsWith("Growing") Then
            lblDate1.Text = "Growing Season Starting Date:"
            cboMonth1.Text = GetSetting(REGAPP, Name, "GrowingMonth1", "March")
            cboDay1.Text = GetSetting(REGAPP, Name, "GrowingDay1", 15)
            cboMonth2.Visible = True
            cboDay2.Visible = True
            cboMonth2.Text = GetSetting(REGAPP, Name, "GrowingMonth2", "November")
            cboDay2.Text = GetSetting(REGAPP, Name, "GrowingDay2", 15)
            tblDate1.Visible = True
            tblDate2.Visible = True
        Else
            tblDate1.Visible = False
            tblDate2.Visible = False
        End If
    End Sub

    Private Sub cboConc_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboConc.SelectedIndexChanged, cboFlow.SelectedIndexChanged, txtMult.TextChanged
        CalcCrossTab = True
    End Sub

    Private Sub cboMonth1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboMonth1.SelectedIndexChanged, cboDay1.SelectedIndexChanged, cboMonth2.SelectedIndexChanged, cboDay2.SelectedIndexChanged, cboAggregation.SelectedIndexChanged
        CalcAggregate = True
    End Sub

    Private Sub cboLoad_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboLoad.SelectionChangeCommitted
        frmCalc.PCodeSelectionCommitted(cboLoad)
    End Sub

    Private Sub cboCCode_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboCCode.SelectionChangeCommitted
        frmCalc.CCodeSelectionCommitted(cboCCode)
    End Sub
End Class
